package com.model.operation;

import com.Table.Movie;
import com.model.DB;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by LPFma on 2016/12/5 0005.
 */
public class MovieOperate {
    public MovieOperate() {
    }

    public static Movie getMovie(ResultSet rs) throws SQLException {
        Integer movieID = rs.getInt("movieID");
        String moviename = rs.getString("moviename");
        Integer userIDitem = rs.getInt("userID");
        Integer coinnum = rs.getInt("coinnum");
        Timestamp uploadtime = rs.getTimestamp("uploadtime");
        String tag = rs.getString("tag");
        String description = rs.getString("description");
        String address = rs.getString("address");
        String picture = rs.getString("picture");
        Integer percent = rs.getInt("percent");
        return new Movie(movieID, moviename, userIDitem, coinnum, uploadtime,
                            tag, description, address, picture, percent);
    }

    public ArrayList<Movie> GetDiffTagMovieList(Integer num, Integer pages, String type) {
        ArrayList<Movie> movies = new ArrayList<Movie>();
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        //查询是否已经加入记录
        try {
            con = getCon();
            StringBuilder getMovieList =
                    //select num videos from the pages's page
                    new StringBuilder("select * from movie " +
                            "where movie.tag = '" + type + "'" +
                            "Limit " + (pages - 1) * num + "," + num);
            pst = con.prepareStatement(getMovieList.toString());
            rs = pst.executeQuery();
            while (rs.next()) {
                movies.add(getMovie(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return movies;
    }

    public ArrayList<Movie> GetHomeVideo() {
        ArrayList<Movie> movies = new ArrayList<Movie>();
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        final int num = 8;

        try {
            con = getCon();
            StringBuilder getMovieList =
                    //select num videos from the pages's page
                    new StringBuilder("select * from movie " +
                            "Order by coinnum" +
                            " Limit " + 0 + "," + num);
            pst = con.prepareStatement(getMovieList.toString());
            rs = pst.executeQuery();
            while (rs.next()) {
                movies.add(getMovie(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return movies;
    }

    public ArrayList<Movie> SearchVideo(String key) {
        ArrayList<Movie> movies = new ArrayList<Movie>();
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        final int num = 10;

        StringBuilder searchKey = new StringBuilder();
        for(int i = 0; i < key.length(); ++i){
            searchKey.append("%");
            searchKey.append(key.charAt(i));
        }
        searchKey.append("%");

        try {
            con = getCon();
            StringBuilder getMovieList =
                    //select num videos from the pages's page
                    new StringBuilder("select * from movie " +
                            " where moviename like '" + searchKey +
                            "' Order by coinnum " +
                            "Limit " + 0 + "," + num );
            pst = con.prepareStatement(getMovieList.toString());
            rs = pst.executeQuery();
            while (rs.next()) {
                movies.add(getMovie(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return movies;
    }

    public ArrayList<Movie> GetHistoryVideo(String username, Integer num, Integer pages, String type) {
        ArrayList<Movie> movies = new ArrayList<Movie>();
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        //查询是否已经加入记录
        try {
            con = getCon();
            //取出用户ID
            StringBuilder getMovieList;
            //select num videos from the pages's page
            if (type.equals("all")) {
                getMovieList = new StringBuilder("select * from user, user_movie_history, movie " +
                        "where user.username = '" + username + "' " +
                        "and user.userID = user_movie_history.userID " +
                        "and movie.movieID = user_movie_history.movieID " +
                        "Limit " + (pages - 1) * num + "," + num);
            } else {
                getMovieList = new StringBuilder("select * from user, user_movie_history, movie " +
                        "where user.username = '" + username + "' " +
                        "adn user.userID = user_movie_history.userID " +
                        "and movie.movieID = user_movie_history.movieID " +
                        "and movie.tag = '" + type + "'" +
                        "Limit " + (pages - 1) * num + "," + num);
            }
            pst = con.prepareStatement(getMovieList.toString());
            rs = pst.executeQuery();
            while (rs.next()) {
                //已经加入记录，返回错误
                movies.add(getMovie(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalArgumentException e) {
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return movies;
    }

    public List<String> getTags() {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        List<String> list = new ArrayList<>();

        try {
            StringBuilder sql = new StringBuilder(
                    "select distinct tag from movie");
            con = getCon();
            pst = con.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            rs = pst.executeQuery();
            while (rs.next()) {
                String tag = rs.getString("tag");
                list.add(tag);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return list;
    }



    public int getRowCount(String table, String tag) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        int rowCount = -1;
        try {
            StringBuilder sql;
            if (tag.equals("all")) {
                sql = new StringBuilder(
                        "select * from " + table);
            }else{
                sql = new StringBuilder(
                        "select * from " + table +
                                " where tag = '" + tag + "'");
            }

            con = getCon();
            pst = con.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            rs = pst.executeQuery();
            rs.last();
            rowCount = rs.getRow();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return rowCount;
    }

    public int getCoinMovie(String username) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        int rowCount = -1;
        try {
            StringBuilder sql = new StringBuilder(
                    "select * from user_movie_coin, user" +
                            "where user.username = '" + username +
                            "' and user.userID = user_movie_coin.userID");
            con = getCon();
            pst = con.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            rs = pst.executeQuery();
            rs.last();
            rowCount = rs.getRow();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return rowCount;
    }

    public int getHistoryMovieCount(String username) {
        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;
        int rowCount = 0;
        try {
            StringBuilder sql = new StringBuilder(
                    "select * from user_movie_history, user " +
                            "where user.username = '" + username +
                            "' and user.userID = user_movie_history.userID");
            con = getCon();
            pst = con.prepareStatement(sql.toString(), ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            rs = pst.executeQuery();
            rs.last();
            rowCount = rs.getRow();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            close(con, pst, rs);
        }
        return rowCount;
    }

    private Connection getCon() throws ClassNotFoundException, SQLException {
        DB.getInstance();
        return DB.getConnection();
    }

    private void close(Connection con, PreparedStatement pst, ResultSet rs) {
        try {
            if (rs != null)
                rs.close();
            if (pst != null)
                pst.close();
            if (con != null)
                DB.release(con);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 私有方法，关闭数据库操作2,用于更新数据库操作
    private void close(Connection con, PreparedStatement pst) {
        try {
            if (pst != null)
                pst.close();
            if (con != null) {
                con.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}
